﻿
namespace SistemaLogistica.DataAccess
{
    using System;
    using System.Collections.Generic;
    using System.Data;
    using System.Data.Common;
    using SistemaLogistica.Business.Entities;

    public class AlquilerVehiculoData : BaseDataAccess
    {
        private AlquilerVehiculoData() { }

        private readonly static AlquilerVehiculoData instancia = new AlquilerVehiculoData();

        public static AlquilerVehiculoData Instancia
        {
            get { return instancia; }
        }

        public List<AlquilerVehiculo> Listar()
        {
            List<DateTime> fechas = new List<DateTime>();
            DbCommand command = db.GetStoredProcCommand("GetFechasAlquileres");

            using (IDataReader dr = db.ExecuteReader(command))
            {
                while (dr.Read())
                {
                    fechas.Add(dr.GetDateTime(dr.GetOrdinal("Fecha")));
                }
            }

            List<AlquilerVehiculo> alquileres = new List<AlquilerVehiculo>();
            command = db.GetStoredProcCommand("GetAqluilerByFecha");
            db.AddInParameter(command, "fecha", DbType.DateTime);

            foreach (DateTime a in fechas)
            {
                db.SetParameterValue(command, "fecha", a);
                using (IDataReader dr = db.ExecuteReader(command))
                {
                    if (dr.Read())
                    {
                        alquileres.Add(new AlquilerVehiculo
                        {
                            Fecha = a,
                            Proveedor = ProveedorData.Instancia.ObtenerPorID(dr.GetInt32(dr.GetOrdinal("IdProveedor")))
                        });
                    }
                }
            }
            return alquileres;
        }

        public List<AlquilerVehiculo> ListarPorFecha(int idProveedor, DateTime fecha)
        {
            List<AlquilerVehiculo> alquileres = new List<AlquilerVehiculo>();
            DbCommand command = db.GetStoredProcCommand("GetAlquileresByProveedorAndFecha");
            db.AddInParameter(command, "idProveedor", DbType.Int32, idProveedor);
            db.AddInParameter(command, "fecha", DbType.DateTime, fecha);

            using (IDataReader dr = db.ExecuteReader(command))
            {
                if (dr.Read())
                {
                    alquileres.Add(new AlquilerVehiculo
                    {
                        Cantidad = dr.GetInt32(dr.GetOrdinal("Cantidad")),
                        Fecha = dr.GetDateTime(dr.GetOrdinal("Fecha")),
                        Comentarios = dr.GetString(dr.GetOrdinal("Comentarios")),
                        Proveedor = ProveedorData.Instancia.ObtenerPorID(dr.GetInt32(dr.GetOrdinal("IdProveedor"))),
                        VehiculoAlquilado = VehiculoData.Instancia.ObtenerAlquilado(dr.GetInt32(dr.GetOrdinal("IdVehiculoAlquilado"))),
                    });
                }
            }
            return alquileres;
        }

        public void Registrar(List<AlquilerVehiculo> alquiler)
        {
            using (DbConnection conection = db.CreateConnection())
            {
                conection.Open();
                using (DbTransaction transaction = conection.BeginTransaction())
                {
                    try
                    {
                        DbCommand command = db.GetStoredProcCommand("InsertAlquilerVehiculo");
                        db.AddInParameter(command, "cantidad", DbType.Int32);
                        db.AddInParameter(command, "comentarios", DbType.String);
                        db.AddInParameter(command, "fecha", DbType.DateTime);
                        db.AddInParameter(command, "idBien", DbType.Int32);
                        db.AddInParameter(command, "idProveedor", DbType.Int32);
                        db.AddInParameter(command, "idVehiculoAlquilado", DbType.Int32);

                        foreach (AlquilerVehiculo a in alquiler)
                        {
                            db.SetParameterValue(command, "cantidad", a.Cantidad);
                            db.SetParameterValue(command, "comentarios", a.Comentarios);
                            db.SetParameterValue(command, "fecha", a.Fecha);
                            db.SetParameterValue(command, "idBien", a.VehiculoAlquilado.IdBien);
                            db.SetParameterValue(command, "idProveedor", a.Proveedor.IdProveedor);
                            db.SetParameterValue(command, "idVehiculoAlquilado", a.VehiculoAlquilado.IdVehiculoAlquilado);
                            db.ExecuteNonQuery(command, transaction);
                        }

                        transaction.Commit();
                    }
                    catch (Exception ex)
                    {
                        transaction.Rollback();
                        throw ex;
                    }
                }
            }
        }

        public void Modificar(List<AlquilerVehiculo> alquiler)
        {
            if (alquiler.Count> 0)
            {
                Eliminar(alquiler[0].Proveedor.IdProveedor, alquiler[0].Fecha);

                using (DbConnection conection = db.CreateConnection())
                {
                    conection.Open();
                    using (DbTransaction transaction = conection.BeginTransaction())
                    {
                        try
                        {
                            DbCommand command = db.GetStoredProcCommand("InsertAlquilerVehiculo");
                            db.AddInParameter(command, "cantidad", DbType.Int32);
                            db.AddInParameter(command, "comentarios", DbType.String);
                            db.AddInParameter(command, "fecha", DbType.DateTime);
                            db.AddInParameter(command, "idBien", DbType.Int32);
                            db.AddInParameter(command, "idProveedor", DbType.Int32);
                            db.AddInParameter(command, "idVehiculoAlquilado", DbType.Int32);

                            foreach (AlquilerVehiculo a in alquiler)
                            {
                                db.SetParameterValue(command, "cantidad", a.Cantidad);
                                db.SetParameterValue(command, "comentarios", a.Comentarios);
                                db.SetParameterValue(command, "fecha", a.Fecha);
                                db.SetParameterValue(command, "idBien", a.VehiculoAlquilado.IdBien);
                                db.SetParameterValue(command, "idProveedor", a.Proveedor.IdProveedor);
                                db.SetParameterValue(command, "idVehiculoAlquilado", a.VehiculoAlquilado.IdVehiculoAlquilado);
                                db.ExecuteNonQuery(command, transaction);
                            }

                            transaction.Commit();
                        }
                        catch (Exception ex)
                        {
                            transaction.Rollback();
                            throw ex;
                        }
                    }
                }
            }
        }

        public void Eliminar(int idProveedor, DateTime fecha)
        {
            List<AlquilerVehiculo> alquiler = ListarPorFecha(idProveedor, fecha);
            using (DbConnection conection = db.CreateConnection())
            {
                conection.Open();
                using (DbTransaction transaction = conection.BeginTransaction())
                {
                    try
                    {
                        DbCommand command = db.GetStoredProcCommand("DeleteAlquilerVehiculo");
                        db.AddInParameter(command, "idBien", DbType.Int32);
                        db.AddInParameter(command, "idProveedor", DbType.Int32);
                        db.AddInParameter(command, "idVehiculoAlquilado", DbType.Int32);

                        foreach (AlquilerVehiculo a in alquiler)
                        {
                            db.SetParameterValue(command, "idBien", a.VehiculoAlquilado.IdBien);
                            db.SetParameterValue(command, "idProveedor", a.Proveedor.IdProveedor);
                            db.SetParameterValue(command, "idVehiculoAlquilado", a.VehiculoAlquilado.IdVehiculoAlquilado);
                            db.ExecuteNonQuery(command, transaction);
                        }
                        transaction.Commit();
                    }
                    catch (Exception ex)
                    {
                        transaction.Rollback();
                        throw ex;
                    }
                }
            }
        }
    }
}
